--
-- @Author:      feilong
-- @DateTime:    2019-12-11 01:29:00
-- @Description: 数据库操作管理
-- 1只对redis数据操作
-- 2对redis操作没找到数据再对mysql操作
-- 3只对mysql数据操作
-- redis用set对mysql表进行映射

local skynet = require "skynet"
local SqlHelper = require "SqlHelper"


local table_insert = table.insert
local table_remove = table.remove

local Command = class("Command")

---------------------------------------------------------
-- Private
---------------------------------------------------------
function Command:ctor(message_dispatch)
	self.message_dispatch = message_dispatch
	self.dbname = skynet.getenv("mysql_db")
	self.schema = {}
	self.mysql = nil
	self.redis = nil
	self.sql_helper = SqlHelper.new()

	self:register()
end

function Command:register()
	self.message_dispatch:registerSelf('start',handler(self,self.start))
	self.message_dispatch:registerSelf('executeMySql',handler(self,self.executeMySql))
	self.message_dispatch:registerSelf('executeMySqlEx',handler(self,self.executeMySqlEx))
	self.message_dispatch:registerSelf('executeRedis',handler(self,self.executeRedis))

	--需要从redis取，取不到再从mysql取的协议
	self.message_dispatch:registerSelf('insert',handler(self,self.intsert))
	self.message_dispatch:registerSelf('delete',handler(self,self.delete))
	self.message_dispatch:registerSelf('update',handler(self,self.update))
	self.message_dispatch:registerSelf('select',handler(self,self.select))
	self.message_dispatch:registerSelf('exists',handler(self,self.exists))
	self.message_dispatch:registerSelf('modify',handler(self,self.modify))

end

--取表的所有字段
function Command:getFields(tbname)
	local sql = string.format("select column_name from information_schema.columns where table_schema = '%s' and table_name = '%s'", self.dbname, tbname)
	local rs = self:executeMySql(sql)
	local fields = {}
	for _, row in pairs(rs) do
		table.insert(fields, row["column_name"])
	end
	return fields
end

--取字段类型
function Command:getFieldType(tbname, field)
	local sql = string.format("select data_type from information_schema.columns where table_schema='%s' and table_name='%s' and column_name='%s'",
			self.dbname, tbname, field)
	local rs = self:executeMySql(sql)
	return rs[1]["data_type"]
end

function Command:getTableFieldType(table_name)
	if not table_name or self.schema[table_name] then 
		return 
	end
	self.schema[table_name] = {}
	local fields = self:getFields(table_name)
	for _, field in pairs(fields) do
		local field_type = self:getFieldType(table_name, field)
		if field_type == "char"
			or field_type == "datetime"
		  	or field_type == "varchar"
		  	or field_type == "tinytext"
		  	or field_type == "text"
		  	or field_type == "mediumtext"
		  	or field_type == "longtext" then
			self.schema[table_name][field] = "string"
		else
			self.schema[table_name][field] = "number"
		end
	end
end

--数据转换
function Command:converRecord(table_name, record)
	self:getTableFieldType(table_name)
	for k, v in pairs(record) do		
		if self.schema[table_name] and self.schema[table_name][k] == "number" then
			record[k] = tonumber(v)
		end
	end
	return record
end

--表中的几个字段做为键在redis中显示
function Command:makeRedisKey(tbname, where_field_values)
    local rediskey = tbname
    for i, values in pairs(where_field_values) do
        for k, v in pairs(values) do 
            if type(v)=='boolean' then 
                if v then 
                    v = 1
                else
                    v = 0
                end
            end             
            rediskey = rediskey..":"..k .. ":" .. v
        end
    end
    return rediskey
end

--执行sql语句
--sync_id 需要队列化操作
function Command:executeMySql(sql, sync_id)
	if not sql then 
		return 
	end
	-- print("________sql___", sql)
	local res = skynet.call(self.mysql, "lua", "mysql_cmd", sync_id, sql)
	local data = {}
	if not res or not next(res) then 
		--mysql取不到数据
		return data
	end
	for _, row in pairs(res) do
		table.insert(data, row)			
	end
	return data	
end

-- table到mysql
function Command:executeMySqlEx(cmd, tbname, field_values, where_field_values, sync_id)
	-- print("____sqlex___",cmd, tbname, field_values, where_field_values, async)
	local sql = nil
	local data = {}
	if cmd == "insert" then 
		sql = self.sql_helper:insertSql(tbname, field_values, where_field_values)
	elseif cmd == "delete" then 
		sql = self.sql_helper:deleteSql(tbname, where_field_values)
	elseif cmd == "update" then 
		sql = self.sql_helper:updateSql(tbname, field_values, where_field_values)
	elseif cmd == "select" then 
		sql = self.sql_helper:selectSql(tbname, field_values, where_field_values)
	elseif cmd == "exists" then 
		sql = self.sql_helper:isExistSql(tbname, where_field_values)
	elseif cmd == "count" then 
		sql = self.sql_helper:countSql(tbname, where_field_values)		
	elseif cmd == "modify" then 		
		sql = self.sql_helper:modifySql(tbname, field_values, where_field_values)
	else
		print("______sql__cmd__error___", cmd)
		return data
	end	
	local res = self:executeMySql(sql, sync_id)	
	if not res or not next(res) then 
		--mysql取不到数据
		return data
	end	
	for _, row in pairs(res) do
		table.insert(data, row)			
	end	
	if #data==1 then 
		return data[1]
	end
	return data	
end

--执行redis命令
--sync_id, 需要sync_id作为队列化操作
--cmd, redis命令（hgetall, hmset ...
--key, 如果是命令没有key的，接其后面的字符串（如 cmd = "EVAL", key = "return {KEYS[1],KEYS[2],ARGV[1],ARGV[2]}"
--field_values 参数列表（直接传table
function Command:executeRedis(sync_id, cmd, key, field_values)  
    -- print("___executeRedis___", sync_id, cmd, key, field_values)  
    field_values = field_values or {}
    local params = self:tableToReds(field_values)
    local result = skynet.call(self.redis, "lua", "redis_cmd", sync_id, cmd, key, table.unpack(params))
    return self:redisToTable(result)
end

--redis结果转为table
function Command:redisToTable(res)
	if not res or type(res) ~= "table" then 
		return res
	end
	local data = {}
	for i=1, #res, 2 do
		data[res[i]] = res[i+1]
	end
	return data
end

--table 转成Redis参数
function Command:tableToReds(tb)
	local params = {}
	for k, v in pairs(tb) do 			
		if type(v) == "table" then 		
			self:tableToReds(v)
		else
			if type(k) ~= "number" then 
				table_insert(params, k)
			end
			table_insert(params, v)
		end
	end
	return params
end








----------------------------------------------------------------------
--CMD
----------------------------------------------------------------------
function Command:start(tables)

	debug_log("__databases_Command:start____", tables)
	--mysql操作服务
	self.mysql = skynet.uniqueservice("mysql_service")
	skynet.call(self.mysql, "lua", "start")
	--redis操作服务
	self.redis = skynet.uniqueservice("redis_service")
	skynet.call(self.redis, "lua", "start")

	debug_log("__数据库服务启动完成____")
end


----------------------------------------------------------------------
--数据库操作
----------------------------------------------------------------------
-- intsert
-- delete
-- update
-- select
-- exists
-- modify

-- 从redis获取表数据，如果不存在，则从mysql加载
-- where_field_values 条件{{name="feilong"},{uid=22}}
-- sync_id 需要同步的uid
function Command:select(sync_id, table_name, where_field_values)	
	
	local redis_key = self:makeRedisKey(table_name, where_field_values)			
	local result = self:executeRedis(sync_id, "hgetall", redis_key)			
	-- redis没有数据返回，则从mysql加载
	if table.empty(result) then
		local sql = self.sql_helper:selectSql(table_name, nil, where_field_values)		
		local data = self:executeMySql(sql, sync_id)		
		if #data == 1 then
			result = data[1]
		else		
			result = data
		end
	end
	if next(result) then 				
		self:executeRedis(sync_id, "hmset", redis_key, result)
		self:executeRedis(sync_id, "expire", redis_key, {86400})
		result = self:converRecord(table_name, result)
	end		
	return result
end

-- redis中增加一行记录，并同步到mysql
-- field_values 表中字段 {user_id = 1, name = "feilong"}
function Command:intsert(sync_id, table_name, field_values)
	local tbname = obj.tbname	
	local sql = self.sql_helper:insertSql(tbname, field_values)
	debug_log(sql)	
	local res = self:executeMySql(sql, sync_id)
	return res
end

-- redis中删除一行记录，并异步到mysql
function Command:delete(sync_id, table_name, where_field_values)
	local redis_key = self:makeRedisKey(table_name, where_field_values)
	self:executeRedis(sync_id, "del", redis_key)
 	local sql = self.sql_helper:deleteSql(table_name, where_field_values)	
 	debug_log("_____delete_____sql_____",sql) 	
	local res = self:executeMySql(sql, sync_id)
	return res
end

-- redis中更新一行记录，并异步到mysql
function Command:update(sync_id, table_name, field_values, where_field_values)
	-- print("___________1111______update_________",row)
	local redis_key = self:makeRedisKey(table_name, where_field_values)
	self:executeRedis(sync_id, "hmset", redis_key, field_values)
	
	local sql = self.sql_helper:updateSql(table_name, field_values, where_field_values)		
	local res = self:executeMySql(sql, sync_id)
	return res
end

--增量加减
function Command:modify(sync_id, table_name, field_values, where_field_values)
	local redis_key = self:makeRedisKey(table_name, where_field_values)
 	debug_log("_____modify___rediskey__row_____",redis_key, row)	
	for k, v in pairs(field_values) do		
		if v ~= 0 then 		
			--hincrby 一次只能操作一个属性
			self:executeRedis(sync_id, "hincrbyfloat", redis_key, k, v)
		end
	end
	local sql = self.sql_helper:modifySql(table_name, field_values, where_field_values)	
	debug_log("_____modify_____sql_____",sql)	
	local res = self:executeMySql(sql, sync_id)
	return res
end

--是否存在
function Command:exists(sync_id, table_name, where_field_values)
	local redis_key = self:makeRedisKey(table_name, where_field_values)
	local result = self:executeRedis(sync_id, "exists", redis_key)
	if result then 
		return true
	end
	local sql = self.sql_helper:isExistSql(table_name, where_field_values)
	result = self:executeMySql(sql, sync_id)
	return result
end

return Command